const sql = require('mssql');

/**
 * 
 *入库接口
 */

//  入库开始时间
let startTime = '2019-04-08 00:00:00';
// let startTime = '';
//  获得订单信息
const getStorageOrder = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY Z.ID) AS px,J.XH,Z.RQ,Z.ID,Z.DWBM,Z.BMBM,Z.BMMC,Z.DWMC,Z.JSFSBM,Z.JSFSMC,Z.ZSL,Z.ZJE,Z.JBRMC,Z.PZH,Z.ZJS,Z.ZSS,Z.ZHJ,J.KM1BM,J.KM2BM,J.KM3BM,J.KMQC FROM DHD_ZB Z LEFT JOIN DHD_JS J ON Z.ID = J.ID LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM LEFT JOIN (SELECT SUM(SL) AS SL,ID FROM DHD_MX GROUP BY ID) M ON Z.ID  = M.ID LEFT JOIN (SELECT SUM(RM.SL) AS BJSL,RZ.ID_DHD FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID GROUP BY RZ.ID_DHD ) T1 ON Z.ID = T1.ID_DHD AND M.SL<=T1.BJSL WHERE Z.RQ <= '${body.searchVal.RQ[1]} 23:59:59' AND Z.RQ >= '${body.searchVal.RQ[0]} 00:00:00' AND RQ >= '${startTime}' AND Z.BMBM='${body.searchVal.BMBM}'  AND T1.ID_DHD IS NULL `;
    let totalSql = `SELECT COUNT(Z.ID) AS TOTAL FROM DHD_ZB Z LEFT JOIN DHD_JS J ON Z.ID = J.ID LEFT JOIN BM_WLDW B ON Z.DWBM = B.BM LEFT JOIN (SELECT SUM(SL) AS SL,ID FROM DHD_MX GROUP BY ID) M ON Z.ID  = M.ID LEFT JOIN (SELECT SUM(RM.SL) AS BJSL,RZ.ID_DHD FROM RKD_MX RM LEFT JOIN RKD_ZB RZ ON RM.ID = RZ.ID GROUP BY RZ.ID_DHD ) T1 ON Z.ID = T1.ID_DHD AND M.SL<=T1.BJSL WHERE Z.RQ <= '${body.searchVal.RQ[1]} 23:59:59' AND Z.RQ >= '${body.searchVal.RQ[0]} 00:00:00' AND RQ >= '${startTime}' AND Z.BMBM='${body.searchVal.BMBM}'  AND T1.ID_DHD IS NULL `
    if (body.searchVal.JSFSBM) {
        startSql += ` AND Z.JSFSBM='${body.searchVal.JSFSBM}'`;
        totalSql += ` AND Z.JSFSBM='${body.searchVal.JSFSBM}'`;
    }
    if (body.searchVal.GYS) {
        startSql += ` AND (B.BM LIKE '%${body.searchVal.GYS}%' OR B.MC LIKE '%${body.searchVal.GYS}%' OR B.JP LIKE '%${body.searchVal.GYS}%') `;
        totalSql += ` AND (B.BM LIKE '%${body.searchVal.GYS}%' OR B.MC LIKE '%${body.searchVal.GYS}%' OR B.JP LIKE '%${body.searchVal.GYS}%')`;
    }
    let endSql = `) AS t1 WHERE t1.px between ${(body.pageNum - 1) * 5 + 1} and ${body.pageNum * 5}`;
    startSql += endSql;
    let result = await sql.query(startSql);
    let total = await sql.query(totalSql);
    ctx.body = {
        data: result.recordset,
        total: total.recordset[0].TOTAL
    };
}
// 把剂型的中文转成英文
const judgeJx = (jx) => {
    let result = '';
    let jxList = {
        P: '片剂',
        J: '胶囊',
        W: '丸剂',
        Y: '液体制剂',
        G: '固体制剂'
    }
    for (let item in jxList) {
        if (jx.replace(/\s+/g, "") == jxList[item]) {
            result = item;
        }
    }
    return result;
}
//  获得订单对应的商品信息
const getStorageOrderShop = async (ctx, next) => {
    const body = ctx.request.body;
    let startSql = `SELECT * FROM (SELECT M.YPBM,M.YPMC,M.YPGG,M.JLDW,M.JXMC,M.SCCJ,M.PZWH,Y.YPLX,SUM(M.SL) AS SL,SUM(M.JE) AS JE,Y.CCTJ,SUM(Y.MJSL) AS MJSL,SUM(M.JS) AS JS,SUM(M.SS) AS SS,M.BZ,M.DJ,M.YPTM,M.CDMC,M.YXQX,M.YJ,M.YJ_HS,M.DJ_HS,SUM(M.HJ) AS HJ,Y.JG_FLAG,Y.YXFL,Y.JP,M.PFJ,M.LSJ,M.HYJ,G.YPPHGZ,L.LSDJ FROM DHD_MX M LEFT JOIN BM_YP Y ON M.YPBM =  Y.BM LEFT JOIN (SELECT  MIN(YPPHGZ) AS YPPHGZ,YPBM FROM YPPHGZ GROUP BY YPBM) G ON M.YPBM = G.YPBM LEFT JOIN (SELECT MIN(DJ) AS LSDJ,YPBM FROM RKD_MX GROUP BY YPBM)L ON M.YPBM = L.YPBM  WHERE M.ID = '${body.orderId}' GROUP BY Y.JP,L.LSDJ,M.YPBM,M.YPMC,M.YPGG,M.JLDW,M.JXMC,M.SCCJ,M.PZWH,Y.YPLX,Y.CCTJ,M.BZ,M.DJ,M.YPTM,M.CDMC,M.YXQX,M.YJ,M.YJ_HS,M.DJ_HS,Y.JG_FLAG,Y.YXFL,M.PFJ,M.LSJ,M.HYJ,G.YPPHGZ) t1 LEFT JOIN (SELECT * FROM (SELECT R.YPBM AS BJYPBM,SUM(R.SL) AS BJSL FROM RKD_MX R LEFT JOIN RKD_ZB Z ON R.ID = Z.ID WHERE Z.ID_DHD = '${body.orderId}' GROUP BY R.YPBM ) t3 WHERE t3.BJYPBM IS NOT NULL) t2 ON t2.BJYPBM = t1.YPBM AND t2.BJSL < t1.SL WHERE t2.BJSL IS NOT NULL OR t1.YPBM <>ALL (SELECT R.YPBM FROM RKD_MX R LEFT JOIN  RKD_ZB Z ON R.ID = Z.ID WHERE Z.ID_DHD = '${body.orderId}')`;
    let result = await sql.query(startSql);
    ctx.body = {
        data: result.recordset
    };
}
// 获得三天前的时间
const getThreeDayAgo = () => {
    let nowStamp = (new Date()).getTime();
    let threeAgoStamp = nowStamp - 3 * 24 * 60 * 60 * 1000;
    let threeAgoTime = new Date(threeAgoStamp);
    let y = threeAgoTime.getFullYear();
    let m = threeAgoTime.getMonth() + 1;
    let d = threeAgoTime.getDate();
    return y + '-' + (m < 10 ? ('0' + m) : m) + '-' + (d < 10 ? ('0' + d) : d);
}

//  给商品分配货位架
const getStorageShopCarNum = async (ctx, next) => {
    const ypbmList = ctx.request.body.ypbmList;
    let matchHwbh = [];
    let reulstYpbmList = {};
    for (let item in ypbmList) {
        let isHwbhKc_kcdtb = await sql.query(`SELECT HWBH FROM KC_KCDTB WHERE YPBM = '${ypbmList[item]}' AND HWBH != '' AND KCSL > 0`);
        if (isHwbhKc_kcdtb.recordset[0]) {
            reulstYpbmList[ypbmList[item]] = isHwbhKc_kcdtb.recordset[0].HWBH;
        }
        else {
            let isHwbhRkd_mx = await sql.query(`SELECT TOP 1 HWBH FROM RKD_MX M LEFT JOIN RKD_ZB Z ON M.ID = Z.ID WHERE Z.SH = 'F' AND M.YPBM = '${ypbmList[item]}'  AND HWBH != '' AND Z.RQ >='${getThreeDayAgo()} 00:00:00.000' ORDER BY Z.RQ DESC `);
            if (isHwbhRkd_mx.recordset[0]) {
                reulstYpbmList[ypbmList[item]] = isHwbhRkd_mx.recordset[0].HWBH;
            } else {
                let isHwbhXtd_mx = await sql.query(`SELECT TOP 1 M.HWBH FROM XTD_MX M LEFT JOIN XTD_ZB Z ON M.ID = Z.ID WHERE Z.SH = 'F' AND M.YPBM = '${ypbmList[item]}'  AND M.HWBH != '' AND Z.RQ >='${getThreeDayAgo()} 00:00:00.000' ORDER BY Z.RQ DESC `);
                if (isHwbhXtd_mx.recordset[0]) {
                    reulstYpbmList[ypbmList[item]] = isHwbhXtd_mx.recordset[0].HWBH;
                } else {
                    let searchShop = await sql.query(`SELECT * FROM YPZL L WHERE L.YPBM = '${ypbmList[item]}'`);
                    let hwlx = '';
                    let hwbhRangeSql = `SELECT H.HWBH FROM HWBH H WHERE H.HWBH <>ALL (SELECT M.HWBH FROM RKD_MX M LEFT JOIN RKD_ZB Z ON M.ID = Z.ID WHERE Z.SH = 'F' AND  M.HWBH != '') AND H.HWBH <>ALL (SELECT HWBH FROM KC_KCDTB K WHERE K.KCSL >0 AND K.HWBH != '' ) `;
                    if (searchShop.recordset.length === 0) {
                        reulstYpbmList[ypbmList[item]] = '';
                    } else {
                        if (searchShop.recordset[0].SPLX === 'Y') {
                            hwlx = searchShop.recordset[0].SPLX + searchShop.recordset[0].NFWY + judgeJx(searchShop.recordset[0].JXMC);
                            hwbhRangeSql += `AND H.HWLX = '${hwlx}' AND H.HWBH LIKE '__${searchShop.recordset[0].CCTJ}%'`
                        } else {
                            hwlx = searchShop.recordset[0].SPLX;
                            hwbhRangeSql += `AND H.HWLX = '${hwlx}' `
                        }
                        hwbhRangeSql += ` ORDER BY H.HWBH`;
                        let hwbhRange = await sql.query(hwbhRangeSql);
                        if (hwbhRange.recordset.length > 0) {
                            hwbhRange.recordset = hwbhRange.recordset.filter(rangeItem => {
                                return !matchHwbh.includes(rangeItem.HWBH.replace(/\s+/g, ""))
                            });
                            if (hwbhRange.recordset.length > 0) {
                                reulstYpbmList[ypbmList[item]] = hwbhRange.recordset[0].HWBH;
                                matchHwbh.push(hwbhRange.recordset[0].HWBH.replace(/\s+/g, ""));
                                if (item === ypbmList.length - 1) {
                                    resolve(reulstYpbmList);
                                }
                            } else {
                                reulstYpbmList[ypbmList[item]] = '';
                            }
                        }
                    }
                }
            }

        }

    }

    ctx.body = {
        success: true,
        data: reulstYpbmList
    };

}
// 补全时间
const repairTime = (time) => {
    return time.toString().length == 1 ? ('0' + time) : time;
}
// 获取时间拼接的年月日
const getNowTimeId = (time) => {
    let nowTime = time ? new Date(time) : new Date();
    let y = nowTime.getFullYear();
    let M = nowTime.getMonth() + 1;
    let d = nowTime.getDate();
    let h = nowTime.getHours();
    let m = nowTime.getMinutes();
    let s = nowTime.getSeconds();
    return {
        msDate: repairTime(m) + repairTime(s),
        date: y.toString().slice(2) + repairTime(M) + repairTime(d),
        fullDate: `${y.toString()}-${repairTime(M)}-${repairTime(d)} ${repairTime(h)}:${repairTime(m)}:${repairTime(s)}.000`
    }
}
// 获得有效期至
const getValidityTimeTo = (m) => {
    let timeStamp = new Date().getTime();
    let trasformStamp = m * 86400000;
    let validityTime = new Date(timeStamp + trasformStamp);
    return getNowTimeId(validityTime).fullDate
}
//  获得订单信息
const keepStorageInfor = async (ctx, next) => {
    // try {
    const body = ctx.request.body;
    let lastOrderSql = `SELECT TOP 1 ID FROM RKD_ZB WHERE ID LIKE '${getNowTimeId().date}071412%' ORDER BY ID DESC`;
    let oldOrder = await sql.query(lastOrderSql);
    let newSerialNum = 1;
    if (oldOrder.recordset.length !== 0) {
        let oldOrderId = oldOrder.recordset[0].ID;
        let oldSerialNum = oldOrderId.slice(oldOrderId.length - 4);
        newSerialNum = Number(oldSerialNum) + 1;
    }
    newSerialNum = newSerialNum.toString();
    newSerialNum = '0000'.slice(0, 4 - newSerialNum.length) + newSerialNum;
    let newId = getNowTimeId().date + '07' + '14' + '12' + newSerialNum;

    let shopInfor = body.entryList;
    let insertMxSql = `INSERT INTO RKD_MX (ID,XH,NB,YPBM,YPTM,YPMC,YPGG,JXMC,SCCJ,CDMC,PZWH,SCPH,SCRQ,YXQX,YXQZ,JLDW,MJSL,SL,JS,SS,YJ,YJ_HS,ZK,DJ,DJ_HS,JE,RT,SE,HJ,ZKJE,CB,JG,FLAG,CKBM,CKMC,HWBH,YXFL,BZ,PFJ,LSJ,HYJ,UID) `;
    // 把时间段转成天数
    let transformTimeToDay = ({ SCRQ, YXQZ }) => {
        let scrqStamp = new Date(SCRQ).getTime();
        let yxqzStamp = new Date(YXQZ).getTime();
        return Math.round((yxqzStamp - scrqStamp) / 86400000)
    }
    shopInfor.forEach(async (item, ind) => {
        let isHavePHGZSql = `SELECT COUNT(YPBM) AS SL FROM YPPHGZ WHERE YPBM = '${item.YPBM}'`;
        let isHavePHGZ = await sql.query(isHavePHGZSql);
        let GzSql = '';
        if (isHavePHGZ.recordset[0].SL === 0) {
            GzSql = `INSERT INTO YPPHGZ VALUES ('${item.YPBM}','${item.YPPHGZ}')`;
        } else {
            GzSql = `UPDATE YPPHGZ SET YPPHGZ = '${item.YPPHGZ}' WHERE YPBM = '${item.YPBM}'`;
        }
        sql.query(GzSql);
        sql.query(`UPDATE BM_YP SET YXQX = '${transformTimeToDay({ SCRQ: item.SCRQ, YXQZ: item.YXQZ })}' WHERE BM= '${item.YPBM}'`)
    });
    let zje = 0, zss = 0, zjs = 0, zsl = 0;

    shopInfor.forEach((item, ind) => {
        let dj = Number(item.DJ ? item.DJ : 0);
        let shsl = Number(item.SHSL ? item.SHSL : 0);
        let mjsl = Number(item.MJSL ? item.MJSL : 0);
        let je = dj * shsl;
        let js = mjsl === 0 ? 0 : Math.floor(shsl / mjsl);
        let ss = shsl - (mjsl * js);
        zje += je;
        zss += ss;
        zjs += js;
        zsl += shsl;
        let xhSerialNum = '00000'.slice(0, 5 - (ind + 1).toString().length) + (ind + 1);
        if (ind == shopInfor.length - 1) {
            insertMxSql += `SELECT '${newId}',${getNowTimeId().msDate + xhSerialNum},${ind + 1},'${item.YPBM}','${item.YPTM}','${item.YPMC}','${item.YPGG}','${item.JXMC}','${item.SCCJ}','${item.CDMC}','${item.PZWH}','${item.SCPH}','${item.SCRQ}',${item.YXQX},'${item.YXQZ}','${item.JLDW}',${item.MJSL},${item.SHSL},${js},${ss},${item.YJ},${item.YJ_HS},100,${item.DJ},${item.DJ_HS},${je},0,0,${je},0,'A','${item.JG_FLAG}','','${item.CCTJ == "常温" ? '0701' : '0702'}','${item.CCTJ}库','${item.HWBH.toUpperCase()}','${item.YXFL}','',${item.PFJ},${item.LSJ},${item.HYJ},'' `;
        } else {
            insertMxSql += `SELECT '${newId}',${getNowTimeId().msDate + xhSerialNum},${ind + 1},'${item.YPBM}','${item.YPTM}','${item.YPMC}','${item.YPGG}','${item.JXMC}','${item.SCCJ}','${item.CDMC}','${item.PZWH}','${item.SCPH}','${item.SCRQ}',${item.YXQX},'${item.YXQZ}','${item.JLDW}',${item.MJSL},${item.SHSL},${js},${ss},${item.YJ},${item.YJ_HS},100,${item.DJ},${item.DJ_HS},${je},0,0,${je},0,'A','${item.JG_FLAG}','','${item.CCTJ == "常温" ? '0701' : '0702'}','${item.CCTJ}库','${item.HWBH.toUpperCase()}','${item.YXFL}','',${item.PFJ},${item.LSJ},${item.HYJ},'' UNION `;
        }
    });
    let insertJsSql = `INSERT INTO RKD_JS (ID,XH,BM,MC,KM1BM,KM2BM,KM3BM,KMBM,KMQC,JSJE,PH) SELECT '${newId}',1,'10','月结','${body.chooseOrderList.KM1BM}','${body.chooseOrderList.KM2BM}','${body.chooseOrderList.KM3BM}','1001','${body.chooseOrderList.KMQC}',${zje},''`;
    let insertZbSql = `INSERT INTO RKD_ZB (ID,RQ,DWBM,DWMC,BMBM,BMMC,JBRBM,JBRMC,JSFSBM,JSFSMC,ZDR,SHR,PZH,ZDZK,FKQX,FKQZ,PJLX,PJHM,ZSL,ZJS,ZSS,ZJE,ZSE,ZHJ,TJE,YJS,EJS,WJS,ZZK,ID_DHD,ZY,BZ,SH) SELECT '${newId}','${getNowTimeId().fullDate}','${body.chooseOrderList.DWBM}','${body.chooseOrderList.DWMC}','${body.chooseOrderList.BMBM}','${body.chooseOrderList.BMMC}','${body.queryList.JBRBM}','${body.queryList.JBRBM === "0717" ? "屠博洲" : "余梅"}','${body.chooseOrderList.JSFSBM ? body.chooseOrderList.JSFSBM : ''}','${body.chooseOrderList.JSFSMC}','${body.queryList.ZDR}','${body.queryList.SHR === "0717" ? "屠博洲" : "余梅"}',NULL,100,${body.queryList.FKQX},'${getValidityTimeTo(body.queryList.FKQX)}','${body.queryList.PJLX}','${body.queryList.PJHM}',${zsl},${zjs},${zss},${zje},0.00,${zje},0.00,${zje},0.00,${zje},0.00,'${body.chooseOrderList.ID}','','','F'`;
    let zbResult = await sql.query(insertZbSql);
    let jsResult = await sql.query(insertJsSql);
    let mxResult = await sql.query(insertMxSql);
    let result = {};
    if (zbResult.rowsAffected[0] > 0 && jsResult.rowsAffected[0] > 0 && mxResult.rowsAffected[0] > 0) {
        let startSql = `SELECT Z.RQ,Z.JSFSMC,W.LLR,Z.ID,Z.DWMC,W.LXR,W.DH,Z.ID_DHD,M.YPMC,M.YPGG,M.JXMC,M.JLDW,M.SCCJ,M.PZWH,M.SCPH,M.YXQZ,M.SL,M.DJ,M.HJ,M.HWBH,Z.ZDR,Z.JBRMC,Z.BZ FROM RKD_MX M LEFT JOIN RKD_ZB Z ON M.ID = Z.ID LEFT JOIN BM_WLDW W ON M.YPBM = W.BM WHERE M.ID = '${newId}'`;
        result = await sql.query(startSql);
    }
    ctx.body = { success: true, result: result.recordset }
    // } catch (err) {
    //     ctx.body = err;
    // }
}


// 为供应商的编码匹配速拓的编码
const getSystemStorageMatchYpbh = async (ctx, next) => {
    const matchExcelList = ctx.request.body.matchExcelList;
    let gypbmList = [];
    matchExcelList.forEach(async (item, ind) => {
        gypbmList.push(`'${item.GYPBM}'`);
    })

    let allListSql = `SELECT * FROM GYSBMPP  WHERE GYPBM IN (${gypbmList.join(',')})`;
    console.log(allListSql);
    let allListResult = await sql.query(allListSql);
    let lastListResult = allListResult.recordset;
    console.log(lastListResult);
    matchExcelList.forEach(async (item1, ind) => {
        let filterVal = lastListResult.filter((item2) => {
            return (item1.BM === item2.BM && item1.GYPBM === item2.GYPBM)
        })
        if (filterVal.length !== 0) {
            item1.YPBM = filterVal[0].YPBM
        }
    })
    ctx.body = matchExcelList;
}



module.exports = {
    'POST /getStorageOrder': getStorageOrder,
    'POST /getStorageOrderShop': getStorageOrderShop,
    'POST /getStorageShopCarNum': getStorageShopCarNum,
    'POST /keepStorageInfor': keepStorageInfor,
    'POST /getSystemStorageMatchYpbh': getSystemStorageMatchYpbh
}
